package com.F09.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.log4j.Logger;
import com.dao.DataOperations;
import com.dbtools.Dispose;
import com.info.OrgInfo;
import com.info.UserInfo;
import com.exception.DeleteException;
import com.exception.InsertException;
import com.exception.SelectException;
import com.exception.UpdateException;
import com.util.CommonTools;

public class OrgUserDAO extends DataOperations {
	/**
	 * ��¼������־;
	 */
	Logger log = Logger.getLogger(F0906DAO.class);

	public OrgUserDAO() {

	}

	public OrgUserDAO(Connection conn) {
		super(conn);
	}

	// orgSQL��ʼ

	// һ��������б��ѯ
	public List selectByOrgLift() throws SelectException {
		List list = new ArrayList();
		StringBuffer sql = new StringBuffer();
		//oracle
//		sql.append("SELECT ORGID,CODE,NAME,PARENTID,ISDEL,SEQ FROM( ");
//		sql.append("SELECT  ORGID, CODE, NAME, PARENTID, ISDEL, LEVEL SEQ ");
//		sql.append("FROM  T_S_ORG CONNECT BY PRIOR ORGID = PARENTID ");
//		sql.append("START WITH PARENTID = '****' ORDER SIBLINGS BY XSXH ASC)  WHERE ISDEL = '0'");
		//sqlserver2005
//		sql.append(" select a.ORGID,a.CODE,a.NAME,a.PARENTID,a.ISDEL,b.SEQ from T_S_ORG as a,");
//		sql.append(" (select PARENTID,ROW_NUMBER() OVER  (ORDER BY PARENTID) as SEQ from "); 
//		sql.append(" (select distinct PARENTID from T_S_ORG) as c) as b ");
//		sql.append(" where a.PARENTID=b.PARENTID  order by a.CODE");
		//sqlserver2000
		sql.append(" SELECT a.ORGID as ORGID,a.CODE as CODE,a.NAME as NAME,a.PARENTID as PARENTID, ");
		sql.append(" a.ISDEL as ISDEL,SEQ=(SELECT COUNT(DISTINCT PARENTID) FROM T_S_ORG WHERE PARENTID<=a.PARENTID) "); 
		sql.append(" FROM T_S_ORG a ORDER BY SEQ ");
		System.out.println("sql="+sql.toString());
		ResultSet rest = null;
		try {
			rest = this.executeQuery(sql.toString());
			while (rest.next()) {
				Map map = new HashMap();
				map.put("ORGID", CommonTools.null2String(rest.getString("ORGID")));
				map.put("CODE", CommonTools.null2String(rest.getString("CODE")));
				map.put("NAME", CommonTools.null2String(rest.getString("NAME")));
				map.put("PARENTID", CommonTools.null2String(rest.getString("PARENTID")));
				map.put("SEQ", CommonTools.null2String(rest.getString("SEQ")));
				list.add(map);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			Dispose.dispose(rest);
		}
		return list;
	}

	// �ж�����ĳ��ڵ��Ƿ����¼�
	public boolean checkChildNode(String code) throws SelectException {
		boolean flag = false;
		StringBuffer sql = new StringBuffer();
		sql.append("select orgid from t_s_org where parentid='" + code
				+ "'");
		ResultSet rest = null;
		try {
			rest = super.executeQuery(sql.toString());
			if (rest.next()) {
				flag = true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (rest != null) {
				Dispose.dispose(rest);
			}
		}
		return flag;
	}
	
	public String queryString(String sql) throws SelectException{
		String name="";
		ResultSet rest = null;
		try {
			rest = this.executeQuery(sql.toString());
			while (rest.next()) {
				name = CommonTools.null2String(rest.getString("NAME"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			Dispose.dispose(rest);
		}
		return name;
	}
	// �б��ѯ
	public List selectByOrg(String key) throws SelectException {
		List list = new ArrayList();
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT ORGID, CODE, NAME, PARENTID, NOTES, ISDEL FROM T_S_ORG ");
		sql.append(" WHERE ISDEL = '0' AND PARENTID = '").append(key).append("'");
		sql.append(" ORDER BY CODE ASC ");
		ResultSet rest = null;
		try {
			rest = this.executeQuery(sql.toString());
			while (rest.next()) {
				Map map = new HashMap();
				map.put("ORGID", CommonTools.null2String(rest.getString("ORGID")));
				map.put("CODE", CommonTools.null2String(rest.getString("CODE")));
				map.put("NAME", CommonTools.null2String(rest.getString("NAME")));
				map.put("PARENTID", CommonTools.null2String(rest.getString("PARENTID")));
				map.put("NOTES", CommonTools.null2String(rest.getString("NOTES")));
				list.add(map);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			Dispose.dispose(rest);
		}
		return list;
	}

	// ɾ��
	public void deleteByKey(String key)
			throws UpdateException {
		List list = new ArrayList();
		key = key.replaceAll(",", "','");
		String orgSql = new StringBuffer().append(" update T_S_ORG set  ISDEL ='1' ").append(" where ORGID in ('" + key + "')").toString();
		String orgParSql = new StringBuffer().append(" update T_S_ORG set  ISDEL ='1' ").append(" where PARENTID in ('" + key + "')").toString();
		list.add(orgSql);
		list.add(orgParSql);
		try {
			super.executeUpdate(list, true);
		} catch (SQLException e) {
			throw new UpdateException(e.getMessage());
		}
	}

	// �ж�����CODE�Ƿ��ظ���ѯ
	public List selectByCode(Object obj) throws SelectException {
		List list = new ArrayList();
		OrgInfo info = (OrgInfo) obj;
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT ORGID, CODE, NAME, PARENTID, NOTES, ISDEL FROM T_S_ORG");
		sql.append(" where ISDEL = '0' AND CODE = '").append(info.getCode()).append("'");
		if (!CommonTools.isNullString(info.getOrgId())) {
			sql.append(" AND ORGID != '" + info.getOrgId() + "' ");
		}
		ResultSet rest = null;
		try {
			rest = this.executeQuery(sql.toString());
			while (rest.next()) {
				Map map = new HashMap();
				map.put("ORGID", CommonTools.null2String(rest.getString("ORGID")));
				list.add(map);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			Dispose.dispose(rest);
		}
		return list;
	}

	// �½�
	public void insert(Object obj)
			throws InsertException {
		OrgInfo info = (OrgInfo) obj;
		String sql = new String();
		List sqlList = new ArrayList();
		sql = " INSERT INTO  T_S_ORG ( ORGID, CODE, NAME, PARENTID, NOTES, ISDEL ) ";
		sql += " VALUES(SEQ_T_S_ORG.nextval,'" + info.getCode() + "','"+ info.getName() + "','" + info.getParentId() + "','"+ CommonTools.convertorQuote(info.getNotes()) + "','0')   ";
		sqlList.add(sql);
		try {
			this.executeUpdate(sqlList, true);
		} catch (SQLException e) {
			throw new InsertException(e.getMessage());
		}
	}

	// �༭һ���¼��ѯ
	public List selectByOrgId(String key) throws SelectException {
		List list = new ArrayList();
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT ORGID, CODE, NAME, PARENTID, NOTES, ISDEL FROM T_S_ORG ");
		sql.append(" where ORGID = '").append(key).append("'");
		ResultSet rest = null;
		try {
			rest = this.executeQuery(sql.toString());
			while (rest.next()) {
				Map map = new HashMap();
				map.put("ORGID", CommonTools.null2String(rest.getString("ORGID")));
				map.put("CODE", CommonTools.null2String(rest.getString("CODE")));
				map.put("NAME", CommonTools.null2String(rest.getString("NAME")));
				map.put("PARENTID", CommonTools.null2String(rest.getString("PARENTID")));
				map.put("NOTES", CommonTools.null2String(rest.getString("NOTES")));
				list.add(map);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			Dispose.dispose(rest);
		}
		return list;
	}

	// �༭
	public void update(Object obj)
			throws UpdateException {
		OrgInfo info = (OrgInfo) obj;
		String sql = new String();
		List sqlList = new ArrayList();
		sql = " update T_S_ORG set ";
		sql += " CODE ='" + info.getCode() + "',NAME='" + info.getName()
				+ "',NOTES='" + CommonTools.convertorQuote(info.getNotes())
				+ "'   ";
		sql += " where ORGID ='" + info.getOrgId() + "'";
		sqlList.add(sql);
		try {
			this.executeUpdate(sqlList, true);
		} catch (SQLException e) {
			throw new UpdateException(e.getMessage());
		}
	}

	// orgSQL����

	// userSQL��ʼ

	// �б��ѯ
	public List selectByUser(String key) throws SelectException {
		List list = new ArrayList();
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT A.USERID, A.CODE, A.NAME, A.PASSWORD, A.ORGID, A.MANAGORGID, A.TEL, A.MOBIL, A.NOTES,B.NAME ORGNAME ");
		sql.append(" FROM T_S_USER A, T_S_ORG B  WHERE A.ISDEL = '0' AND A.ORGID= B.ORGID AND A.ORGID = '").append(key).append("'");
		sql.append(" ORDER BY A.USERID ASC ");
		ResultSet rest = null;
		try {
			rest = this.executeQuery(sql.toString());
			while (rest.next()) {
				Map map = new HashMap();
				map.put("USERID", CommonTools.null2String(rest.getString("USERID")));
				map.put("CODE", CommonTools.null2String(rest.getString("CODE")));
				map.put("NAME", CommonTools.null2String(rest.getString("NAME")));
				map.put("PASSWORD", CommonTools.null2String(rest.getString("PASSWORD")));
				map.put("ORGID", CommonTools.null2String(rest.getString("ORGID")));
				map.put("MANAGORGID", CommonTools.null2String(rest.getString("MANAGORGID")));
				map.put("TEL", CommonTools.null2String(rest.getString("TEL")));
				map.put("MOBIL", CommonTools.null2String(rest.getString("MOBIL")));
				map.put("NOTES", CommonTools.null2String(rest.getString("NOTES")));
				map.put("ORGNAME", CommonTools.null2String(rest.getString("ORGNAME")));
				list.add(map);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			Dispose.dispose(rest);
		}
		return list;
	}

	// �ж�����CODE�Ƿ��ظ���ѯ
	public List selectByUserCode(Object obj) throws SelectException {
		List list = new ArrayList();
		UserInfo info = (UserInfo) obj;
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT USERID, CODE, NAME, PASSWORD, ORGID, MANAGORGID, TEL, MOBIL, NOTES, ISDEL FROM T_S_USER");
		sql.append(" where ISDEL = '0' AND CODE = '").append(info.getCode()).append("'");
		if (!CommonTools.isNullString(info.getUserId())) {
			sql.append(" AND USERID != '" + info.getUserId() + "' ");
		}
		ResultSet rest = null;
		try {
			rest = this.executeQuery(sql.toString());
			while (rest.next()) {
				Map map = new HashMap();
				map.put("USERID", CommonTools.null2String(rest.getString("USERID")));
				list.add(map);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			Dispose.dispose(rest);
		}
		return list;
	}

	// �½�
	public void insertUser(Object obj)
			throws InsertException {
		UserInfo info = (UserInfo) obj;
		String sql = new String();
		List sqlList = new ArrayList();
		sql = " INSERT INTO  T_S_USER (CODE, NAME, PASSWORD, ORGID, MANAGORGID, TEL, MOBIL, NOTES, ISDEL,IP) ";
		sql += " VALUES('" + info.getCode() + "','"
				+ info.getName() + "','" + info.getPassword() + "','"
				+ info.getOrgId() + "','" + info.getManagOrgId() + "','"
				+ info.getTel() + "','" + info.getMobil() + "','"
				+ CommonTools.convertorQuote(info.getNotes()) + "','0','"
				+ info.getIP() + "')   ";
		sqlList.add(sql);
		try {
			this.executeUpdate(sqlList, true);
		} catch (SQLException e) {
			throw new InsertException(e.getMessage());
		}
	}

	// �༭һ���¼��ѯ
	public List selectByUserId(String key) throws SelectException {
		List list = new ArrayList();
		StringBuffer sql = new StringBuffer();
		sql
				.append("SELECT USERID, CODE, NAME, PASSWORD, ORGID, MANAGORGID, TEL, MOBIL, NOTES, ISDEL,IP FROM T_S_USER ");
		sql.append(" where USERID = '").append(key).append("'");
		ResultSet rest = null;
		try {
			rest = this.executeQuery(sql.toString());
			while (rest.next()) {
				Map map = new HashMap();
				map.put("USERID", CommonTools.null2String(rest
						.getString("USERID")));
				map
						.put("CODE", CommonTools.null2String(rest
								.getString("CODE")));
				map
						.put("NAME", CommonTools.null2String(rest
								.getString("NAME")));
				map.put("PASSWORD", CommonTools.null2String(rest
						.getString("PASSWORD")));
				map.put("ORGID", CommonTools.null2String(rest
						.getString("ORGID")));
				map.put("MANAGORGID", CommonTools.null2String(rest
						.getString("MANAGORGID")));
				map.put("TEL", CommonTools.null2String(rest.getString("TEL")));
				map.put("MOBIL", CommonTools.null2String(rest
						.getString("MOBIL")));
				map.put("NOTES", CommonTools.null2String(rest
						.getString("NOTES")));
				map.put("IP", CommonTools.null2String(rest.getString("IP")));
				String MorgId = CommonTools.null2String(rest
						.getString("MANAGORGID"));
				List tempList1 = super
						.getDataFromDatabase("select CODE,NAME from T_S_ORG where orgid in('"
								+ MorgId.replaceAll(",", "','") + "')");
				map.put("MANAGORGNAME", this.transName(tempList1));

				list.add(map);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			Dispose.dispose(rest);
		}
		return list;
	}

	private String transName(List list) {
		String returnName = "";
		for (int i = 0; i < list.size(); i++) {
			Map map = (Map) list.get(i);
			returnName = returnName + "(" + (String) map.get("CODE") + ")"
					+ (String) map.get("NAME") + ",";
		}
		if (returnName.length() != 0) {
			returnName = returnName.substring(0, returnName.length() - 1);
		}
		return returnName;
	}

	// �༭
	public void updateUser(Object obj)
			throws UpdateException {
		UserInfo info = (UserInfo) obj;
		String sql = new String();
		List sqlList = new ArrayList();
		sql = " update T_S_USER set ";
		sql += " CODE ='" + info.getCode() + "',NAME='" + info.getName()
				+ "',PASSWORD='" + info.getPassword() + "',MANAGORGID='"
				+ info.getManagOrgId() + "',TEL='" + info.getTel()
				+ "',MOBIL='" + info.getMobil() + "',NOTES='"
				+ CommonTools.convertorQuote(info.getNotes()) + "',IP='"
				+ info.getIP() + "'   ";
		sql += " where USERID ='" + info.getUserId() + "'";
		sqlList.add(sql);
		try {
			super.executeUpdate(sqlList, true);
		} catch (SQLException e) {
			throw new UpdateException(e.getMessage());
		}
	}

	// ɾ��
	public void deleteByUserKey(String key)
			throws UpdateException {
		List list = new ArrayList();
		key = key.replaceAll(",", "','");
		String userSql = new StringBuffer().append(
				" update T_S_USER set  ISDEL ='1' ").append(
				" where USERID in ('" + key + "')").toString();
		String userRoleSql = new StringBuffer().append(
				" delete from T_S_USER_ROLE ").append(
				" where USERID in ('" + key + "')").toString();
		list.add(userSql);
		list.add(userRoleSql);
		try {
			super.executeUpdate(list, true);
		} catch (SQLException e) {
			throw new UpdateException(e.getMessage());
		}
	}

	// �����ɫ������б��ѯ
	public List selectByUserLift(String key) throws SelectException {
		List list = new ArrayList();
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT A.ROLEID, A.CODE, A.NAME FROM T_S_ROLE A ");
		sql
				.append(" WHERE  A.ROLEID NOT IN (SELECT A.ROLEID FROM T_S_ROLE A,T_S_USER_ROLE B ");
		sql.append(" WHERE A.ROLEID = B.ROLEID AND B.USERID='").append(key)
				.append("')");
		sql.append(" ORDER BY  A.CODE ASC");
		ResultSet rest = null;
		try {
			rest = this.executeQuery(sql.toString());
			while (rest.next()) {
				Map map = new HashMap();
				map.put("ROLEID", CommonTools.null2String(rest
						.getString("ROLEID")));
				map
						.put("CODE", CommonTools.null2String(rest
								.getString("CODE")));
				map
						.put("NAME", CommonTools.null2String(rest
								.getString("NAME")));
				list.add(map);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			Dispose.dispose(rest);
		}
		return list;
	}

	// �����ɫ���Ҳ��б��ѯ
	public List selectByUserRight(String key) throws SelectException {
		List list = new ArrayList();
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT A.ROLEID, A.CODE, A.NAME  ");
		sql.append(" FROM T_S_ROLE A,T_S_USER_ROLE B  ");
		sql.append(" WHERE A.ROLEID = B.ROLEID AND B.USERID='").append(key)
				.append("' ");
		sql.append(" ORDER BY  A.CODE ASC ");
		ResultSet rest = null;
		try {
			rest = this.executeQuery(sql.toString());
			while (rest.next()) {
				Map map = new HashMap();
				map.put("ROLEID", CommonTools.null2String(rest
						.getString("ROLEID")));
				map
						.put("CODE", CommonTools.null2String(rest
								.getString("CODE")));
				map
						.put("NAME", CommonTools.null2String(rest
								.getString("NAME")));
				list.add(map);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			Dispose.dispose(rest);
		}
		return list;
	}

	/* �½���Ȩ�������
	 * Modify by liusm  2008-12-18 ��ݿ�ִ���б����Ӧ��forѭ���ⲿ
	 */
	public void insertUserRole(String key, String userId) throws InsertException {
		key = key.substring(0, key.lastIndexOf(","));
		String s[] = key.split(",");
		String keyId = "";
		List sqlList = new ArrayList();
		for (int i = 0; i < s.length; i++) {
			keyId = s[i];
			String sql = new String();
			sql = " INSERT INTO  T_S_USER_ROLE ( USERID,ROLEID ) ";
			sql += " VALUES('" + userId + "','" + keyId + "')   ";
			sqlList.add(sql);
		}
		try {
			this.executeUpdate(sqlList, true);
		} catch (SQLException e) {
			throw new InsertException(e.getMessage());
		}
	}

	// ɾ����Ȩ�������
	public void deleteByUserRole(String key, String userId) throws DeleteException {
		key = key.substring(0, key.lastIndexOf(","));
		key = key.replaceAll(",", "','");
		StringBuffer sql = new StringBuffer();
		List sqlList = new ArrayList();
		sql.append("delete T_S_USER_ROLE  WHERE ROLEID = ANY ('");
		sql.append(key).append("') and USERID = '").append(userId).append("'");
		sqlList.add(sql.toString());
		try {
			super.executeUpdate(sqlList, true);
		} catch (SQLException e) {
			throw new DeleteException(e.getMessage());
		}
	}

	// userSQL����

	// �������
	// ��ѯ����
	public List selectByPassword(String key) throws SelectException {
		List list = new ArrayList();
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT password,tel,mobil,notes  ");
		sql.append(" FROM T_S_USER ");
		sql.append(" WHERE userid='").append(key).append("' ");
		ResultSet rest = null;
		try {
			rest = this.executeQuery(sql.toString());
			while (rest.next()) {
				// password = rest.getString("password");
				Map map = new HashMap();
				map.put("PASSWORD", CommonTools.null2String(rest
						.getString("password")));
				map.put("TEL", CommonTools.null2String(rest.getString("tel")));
				map.put("MOBIL", CommonTools.null2String(rest
						.getString("mobil")));
				map.put("NOTES", CommonTools.null2String(rest
						.getString("notes")));
				list.add(map);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			Dispose.dispose(rest);
		}
		return list;
	}

	// �༭ 0------ԭ���벻��ȷ 1------�޸ĳɹ� 2------��ݿ����
	public String updatePassword(Object obj)
			throws UpdateException {
		UserInfo info = (UserInfo) obj;
		ResultSet rest = null;
		boolean flag = false;
		StringBuffer sql = new StringBuffer();
		sql.append("select 1 from T_S_USER where USERID= ").append(
				info.getUserId());
		sql.append(" and PASSWORD='").append(info.getOldpassword()).append("'");
		try {
			log.info(sql.toString());
			rest = super.executeQuery(sql.toString());
			if (rest.next()) {
				sql = new StringBuffer();
				sql.append(" update T_S_USER set ");
				sql.append(" PASSWORD ='").append(info.getPassword());
				sql.append("',TEL='").append(info.getTel());
				sql.append("',MOBIL='").append(info.getMobil());
				sql.append("',NOTES='").append(
						CommonTools.convertorQuote(info.getNotes()));
				sql.append("' where USERID ='").append(info.getUserId())
						.append("'");
				flag = super.executeUpdate(sql.toString());
			} else {
				return "0";
			}
		} catch (Exception e) {
			e.printStackTrace();
			return "2";
		} finally {
			Dispose.dispose(rest);
		}
		if (flag) {
			return "1";
		}
		return "2";
	}

	public boolean updateUserInfo(Object obj)
			throws UpdateException {
		UserInfo info = (UserInfo) obj;
		boolean flag = false;
		StringBuffer sql = new StringBuffer();
		sql.append(" update T_S_USER set ");
		sql.append(" TEL='").append(info.getTel());
		sql.append("',MOBIL='").append(info.getMobil());
		sql.append("',NOTES='").append(
				CommonTools.convertorQuote(info.getNotes()));
		sql.append("' where USERID ='").append(info.getUserId())
				.append("'");
		try{
			flag = super.executeUpdate(sql.toString());
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return flag;
	}

	/*
	 * 0------ԭ���벻��ȷ 1------�޸ĳɹ� 2------��ݿ����
	 */
	public String updatePassword(String id, String old, String newpass) {
		ResultSet rest = null;
		boolean flag = false;
		StringBuffer sql = new StringBuffer();
		sql.append("select 1 from T_S_USER where USERID= ").append(id);
		sql.append(" and PASSWORD='").append(old).append("'");
		try {
			log.info(sql.toString());
			rest = super.executeQuery(sql.toString());
			if (rest.next()) {
				sql = new StringBuffer();
				sql.append("update T_S_USER PASSWORD='").append(newpass)
						.append("'");
				sql.append(" where USERID=").append(id);
				flag = super.executeUpdate(sql.toString());
			} else {
				return "0";
			}
		} catch (Exception e) {
			e.printStackTrace();
			return "2";
		} finally {
			Dispose.dispose(rest);
		}
		if (flag) {
			return "1";
		}
		return "2";
	}
	// ������� ����
}
